--首页标题大logo的表
create table bigflv(
       bid int not null ,--flash图片的id
       bname varchar2(4000),
       bimg varchar2(4000), --相册的路径
	   bdate date, --用来自动定期切换图片
       primary key(bid),--设置主键
       text1 varchar2(4000),--预留字段
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
select * from bigflv
delete from bigflv where naid=1011; 
create sequence seq_bigflv_bid start with 1000 increment by 1;
insert into bigflv values (1000,'nihao',null,sysdate,null,null,null);
select * from (select a.*,rownum rn from (select * from bigflv order by bid)a where rownum<=10) b where rn>0
--新闻部分--------加新闻发布的日期
create table newsinfo(
       nlid int not null , --新闻id
 	   nltype varchar2(4000),-- 新闻类别
       nltitle varchar2(4000), --新闻标题
	   nlcontents varchar2(4000),--新闻内容
       nlimg varchar2(4000), -- 新闻图片路
	   nlimgName varchar2(4000), --新闻图片的名字
   	   nldate date ,--新闻发布的日期
       primary key(nlid),
       text1 varchar2(4000),--预留字段
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
create sequence seq_newsinfo_nlid start with 1000 increment by 1;
select * from newsinfo where nltype='公司新闻'
select * from (select a.*,rownum rn from (select * from newsInfo order by nlid )a where rownum<=10) b where rn>0
insert into newsinfo values(seq_newsinfo_nlid.nextval,'新闻热点','像女王一样宠爱自己','所以很多时候拍不出自然唯美的感觉，反倒拍了很多的酒菜、红包。在这种大环境下也是为什么国内的新人都选择在婚礼前拍婚纱照的原因之一吧。... ');
delete from newsinfo where nltype='公司新闻'
insert into newsInfo values(seq_newsleft_nlid.nextval,'','','','','',to_date('2015-12-05','yyyy-MM-dd'),'','','')
alter table newsinfo rename column ndate to nldate;  --楼层

--最新作品部分
create table newart(
       naid int not null ,--封面图片的id
	   napic varchar2(4000),--封面图片
       naname varchar2(4000),--封面图片的名字
	   nadate date,--上传日期
	   natime date,--更新时间
       naContentImg varchar2(4000),--封面点进去的相册里面的图片
       primary key(naid),
       text1 varchar2(4000),--预留字段
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
create sequence seq_newart_naid start with 1000 increment by 1;
update newart set naContentImg='../upload/144983250118486.gif,../upload/1449832501184231.gif' where naid=1028;
insert into newart values(seq_newart_naid.nextval,'jj','美丽',sysdate,sysdate,'11','1','1','1');
select * from newart;
delete from newart where naid=1060;
delete from newart where naid>0; 
update newart set naid=1037,naContentImg='../dataInfo/1450777829889306.gif',naname='qqq1' where naid=1037;
select * from (select a.*,rownum rn from (select * from newart where to_char(natime,'yyyy-MM-dd hh24:mi:ss') LIKE '%25%' order by naid)a where rownum<=3) b where rn>1;
SELECT * FROM newart WHERE to_char(natime,'yyyy-MM-dd hh24:mi:ss') LIKE '%2015-12-27%'
--特色场景部分
create table specialSpace(
       ssid int not null, --图片的id
       ssname varchar2(4000),--封面图片的描述
       sspath varchar2(4000),
       ssClickPath varchar2(4000),--点击的路径
       primary key(ssid),
       text1 varchar2(4000),--预留字段
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
create sequence seq_specialSpace_ssid start with 1000 increment by 1;
select * from specialSpace;

--特色场景部分下面图片
create table underSpace(
       ussid int not null,--图片的id
       ussname varchar2(4000),--封面图片的名字
       usnameHover varchar2(4000), --鼠标移上去的时候显示的图片路径
       uspath varchar2(4000),--图片的路径
       primary key(ussid),
       text1 varchar2(4000),--预留字段
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
create sequence seq_underSpace_usid start with 1000 increment by 1;
select * from underSpace;



--服务团队部分
create table servicesGroup(
       sgid int not null,--人物头像id
       sgname varchar2(4000),--人物姓名
       sgimgpath varchar2(4000),--人物头像路径
       sgclickpath varchar2(4000),--点击的时候的路径
	   sgtype varchar2(4000), --类别
       primary key(sgid),
       text1 varchar2(4000),--国家
       text2 varchar2(4000),--预留字段
       text3 varchar2(4000)--预留字段
)
create sequence seq_servicesGroup_sgid start with 1000 increment by 1;
insert into servicesGroup values (1001,'文怡','123','123','摄影团队',null,null,null);
select * from servicesGroup
select * from (select a.*,rownum rn from (select * from servicesGroup order by sgid)a where rownum<=5) b where rn>0
update servicesGroup set sgid=sgid,sgname='文怡1',sgtype='化妆师团队' where sgid=1007;
--评论部分  
create table CommentsInfo(
	csid int ,--评论的id
	usid int 
		constraint FK_userInfo_comments_usid references userInfo(usid),--评论人的id
	naid int 
		constraint FK_Comments_newart_usid references newart(naid),--评论的照片的id 
	cscontents varchar2(4000), --评论的内容
	csdate date, --评论的时间
	temp varchar2(4000),--备注
    text1 varchar2(4000),--预留字段
    text2 varchar2(4000)--预留字段
)
create sequence seq_comments_csid start with 1000 increment by 1;
select * from CommentsInfo where csid=1113;

delete from CommentsInfo where csid=1113 and csid>1181;

alter table CommentsInfo rename column floor to text1;  --楼层

--预定部分
create table reserve (
	reid int primary key,--预订id
	usid int 
		constraint FK_userInfo_reserve_reid references userInfo(usid), --预定人的id
	aiid int 
		constraint ActivitiesInfo_reserve_reid references ActivitiesInfo(aiid),--预定活动套餐的id
	renames varchar2(4000),--客户姓名
	ainame varchar2(4000),--活动名称
	retel varchar2(4000),
	redate date, --预定的时间
	text1 varchar2(4000),--预留字段
    text2 varchar2(4000),--预留字段
    text3 varchar2(4000)--预留字段
)
create sequence seq_reserve_reid start with 1000 increment by 1;
select * from reserve;
ALTER TABLE reserve RENAME COLUMN text1 TO rename;
insert into reserve values(seq_reserve_reid.nextval,1040,1000,'你好','123123',null,null,null,null);
select * from reserve
delete from reserve where aiid=1000;
ALTER TABLE reserve DROP CONSTRAINT FK_userInfo_reserve_reid;
ALTER TABLE reserve DROP CONSTRAINT ActivitiesInfo_reserve_reid;
drop table reserve
--会员信息表
create table userInfo(
	usid int primary key,  --会员编号
	uname varchar2(4000),  --会员名
	pwd varchar2(4000),  --密码
	tel varchar2(4000),  --联系方式
	email varchar2(4000) unique,  --邮箱
	province varchar2(4000),  --省份
	city varchar2(4000),  --城市
	area varchar2(4000),  --地区
	udate date,  --注册时间
	status int,  --状态（0.未激活   1.正常账号）
	temp varchar2(4000),  --备用字段s
	flag varchar2(4000)  --备用字段
);
create sequence seq_userInfo_usid start with 1000 increment by 1;
select * from userInfo ;
select seq_userInfo_usid.nextval from dual
drop table userInfo;
drop sequence seq_userInfo_usid;
insert into userinfo values(seq_userInfo_usid.nextval,'任燕婷','123456','18274716359','2170500570@qq.com','湖南省','岳阳市','岳阳县',sysdate,1,'','');

select * from userinfo where email='596144600@qq.com' 
delete from  userinfo where email='714468985@qq.com' 

insert into userinfo values(1236,'燕婷','123456','18274716359','1243624540@qq.com','湖南省','岳阳市','岳阳县',sysdate,1,'','');
insert into userinfo values(1436,'燕婷','123456','18274716359','1243624440@qq.com','湖南省','岳阳市','岳阳县',sysdate,1,'','');
insert into userinfo values(1447,'燕婷','123456','18274716359','1243327823@qq.com','湖南省','岳阳市','岳阳县',sysdate,1,'','');
insert into userinfo values(1458,'燕婷','123456','18274716359','1243324240@qq.com','湖南省','岳阳市','岳阳县',sysdate,1,'','');

--管理员信息表
create table AdminInfo(
	aid int primary key, --编号
	aname varchar2(100), --名字
	pwd varchar2(20), --密码
	status int  --状态
)
create sequence seq_admininfo_aid start with 1000 increment by 1;
delete from table admininfo; 
drop table admininfo;
drop sequence seq_admininfo_aid
insert into admininfo values(seq_admininfo_aid.nextval,'燕婷','123',1)
select * from admininfo;


--微电影
create table shortmovie(
	smid int primary key,--视频编号
	snmovie varchar2(4000) ,--视频
	smname varchar2(4000),--客户姓名
	smdate date,
	text1  varchar2(4000) ,--备用字段
	text2  varchar2(4000) ,--备用字段
	text3  varchar2(4000) --备用字段
);
create sequence seq_shortmovie_smid start with 1000 increment by 1;
insert into shortmovie values(seq_shortmovie_smid.nextval,'1','1',to_date('2015-02-03','yyyy-MM-dd'),null,null,null)
select * from shortmovie
select * from shortmovie where smdate= to_date('2015-12-27 4:55:05','yyyy-MM-dd hh24:mi:ss')
select * from (select a.*,rownum rn from (select * from shortmovie order by smid)a where rownum<=10) b where rn>0


--公司套餐活动
create table ActivitiesInfo(
	aiid int primary key,--活动的id
	ainame varchar2(4000),--活动的名称
	aicontent varchar2(4000),--活动的内容
	aisdate date,--预留字段
	aiedate date,--预留字段
	text3 varchar2(4000)--预留字段
)
select aiid from activitiesInfo where ainame='你好世界';
create sequence seq_ActivitiesInfo_aiid start with 1000 increment by 1;
insert into activitiesInfo values(seq_activitiesInfo_aiid.nextval,'双12','更多优惠',to_date('2015-12-1','yyyy-mm-dd'),to_date('2015-12-15','yyyy-mm-dd'),'')
select * from ActivitiesInfo

select re.aiid,re.renames,re.retel,re.redate from reserve re inner join  ActivitiesInfo ao on re.aiid=ao.aiid inner join userinfo uo on re.usid=uo.usid;
select re.reid ,ao.aiid,uo.uname,uo.tel,re.redate from reserve re inner join  ActivitiesInfo ao on re.aiid=ao.aiid inner join userinfo uo on re.usid=uo.usid
select ainame,aicontent,aisdate from ActivitiesInfo